﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Spreadsheet;

namespace OPENXMLLib
{
    public static class CellExtention
    {
        /// <summary>
        /// 获取单元格所在的行对象
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static Row GetRow(this Cell cell)
        {
            return cell.Parent as Row;
        }

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        public static object GetValue(this Cell cell)
        {
            SharedStringTable stringTable = cell.GetRow().GetSheetData().GetWorksheetPart().GetWorkbookPart().GetStringTable();

            //因为Excel的数据存储在SharedStringTable中,须要获取数据在SharedStringTable 中的索引
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                {
                    return value;
                }
                if (string.IsNullOrEmpty(Convert.ToString(cell.CellValue)))
                {
                    return value;
                }
                double num;
                if (double.TryParse(cell.CellValue.Text, out num))
                {
                    if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                    {
                        value = stringTable.ChildElements[Int32.Parse(cell.CellValue.Text)].InnerText;
                    }
                    else
                    {
                        return num.ToString();
                    }
                }
                else
                {
                    return cell.CellValue.Text;
                }
            }
            catch (Exception ex)
            {
                //value = string.Empty;
            }
            return value;
        }

        public static string ToAA(int value)
        {
            string AA = string.Empty;
            int d = value / 26;
            int remainder = value % 26;

            if (d == 1 && remainder == 0)
            {
                AA = ToA(26) + AA;
                d = 0;
            }
            else
            {
                AA = ToA(remainder) + AA;
            }

            while (d > 0)
            {
                remainder = d % 26;
                d = d / 26;
                AA = ToA(remainder) + AA;
            }
            return AA;
        }

        private static string ToA(int value)
        {
            if (value <= 0)
            {
                return string.Empty;
            }
            return new string((char)('A' + (value - 1)), 1);
        }



        public static string XGetValue(this Cell cell)
        {
            SharedStringTable stringTable = cell.GetRow().GetSheetData().GetWorksheetPart().GetWorkbookPart().GetStringTable();//.XGetSheetData().XGetWorksheetPart().XGetWorkbookPart().XGetStringTable();

            string value = string.Empty;
            if (cell.CellValue != null)
            {
                if (cell.DataType == null)
                {
                    if (cell.CellValue != null)
                    {
                        value = cell.CellValue.Text;
                    }
                }
                else
                {
                    if (cell.DataType == CellValues.SharedString)
                    {
                        int si = Convert.ToInt32(cell.CellValue.Text);
                        value = stringTable.ChildElements[si].InnerText;
                    }
                    else
                    {
                        value = cell.CellValue.Text;
                    }
                }
            }
            return value;
        }

        public static void XSetValue(this Cell cell, object value)
        {
            decimal dValue = 0;
            if (decimal.TryParse(value.ToString(), out dValue))
            {
                if (value.ToString().StartsWith("0"))
                {
                    if (value.ToString().Contains("."))
                    {
                        XSetDecimalValue(cell, 0);
                    }
                    else
                    {
                        XSetStringValue(cell, value.ToString());
                    }
                }
                else
                {
                    XSetDecimalValue(cell, dValue);
                }
            }
            else
            {
                XSetStringValue(cell, value.ToString());
            }
        }

        public static void XSetSharedStringValue(this Cell cell, string value)
        {
            SharedStringTable stringTable = cell.GetRow().GetSheetData().GetWorksheetPart().GetWorkbookPart().GetStringTable();

            int si = -1;
            for (int index = 0; index < stringTable.ChildElements.Count; index++)
            {
                if (stringTable.ChildElements[index].InnerText == value)
                {
                    si = index;
                    break;
                }
            }
            if (si != -1)
            {
                cell.CellValue = new CellValue(si.ToString());
                cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.SharedString);
            }
            else
            {
                SharedStringItem stringItem = new SharedStringItem();
                Text text = new DocumentFormat.OpenXml.Spreadsheet.Text(value);
                stringItem.AppendChild(text);
                stringTable.AppendChild(stringItem);
                si = stringTable.ChildElements.Count - 1;

                cell.DataType = new DocumentFormat.OpenXml.EnumValue<CellValues>(CellValues.SharedString);

                CellValue cellValue = cell.CellValue;
                if (cellValue == null)
                {
                    cellValue = new CellValue();
                    cell.Append(cellValue);
                }
                cellValue.Text = si.ToString();
                stringTable.Count.Value++;
                stringTable.UniqueCount.Value++;
            }
        }

        public static void XSetStringValue(this Cell cell, string value)
        {
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue();
            cell.CellValue.Text = value;
        }

        public static void XSetDecimalValue(this Cell cell, decimal value)
        {
            cell.CellValue = new CellValue(value.ToString());
            cell.DataType = CellValues.Number;
        }
    }
}
